{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 数据分析流程\n",
    "1. 数据收集\n",
    "2. 数据清洗\n",
    "3. 数据分析\n",
    "4. 数据可视化"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "7d9f5cfd6d121ad3"
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "# series 创建\n",
    "import pandas as pd\n",
    "import numpy as np"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T05:46:30.068161100Z",
     "start_time": "2025-08-27T05:46:29.587153800Z"
    }
   },
   "id": "1bc32534d4662476"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 数据导入导出"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "67a8376456250713"
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "加载csv :     day  1   2   3   4  5  6\n",
      "0   Fri  1  16   1   1  0  0\n",
      "1  Stat  2  53  18  13  1  0\n",
      "2   Sun  0  39  15  18  3  1\n",
      "3  Thur  1  48   4   5  1  3\n",
      "写入csv : None\n"
     ]
    }
   ],
   "source": [
    "# 加载csv\n",
    "df = pd.read_csv('../../resources/tips.csv')\n",
    "print('加载csv :', df)\n",
    "# 写入csv\n",
    "result = df.to_csv('../../resources/tips_out.csv', index=False)\n",
    "print('写入csv :', result)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T01:19:17.324695100Z",
     "start_time": "2025-08-27T01:19:17.309721600Z"
    }
   },
   "id": "7ab14dd28510b1c9"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# json 数据处理\n",
    "json_data = pd.read_json('../../resources/tips.json')\n",
    "print('json 数据处理 :', json_data)\n",
    "result = json_data.to_json('../../resources/tips_out.json')\n",
    "print('json 数据处理 :', result)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "9a139cc75f09c6e5"
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: '../../resources/tips.xlsx'",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mFileNotFoundError\u001B[0m                         Traceback (most recent call last)",
      "Cell \u001B[1;32mIn[6], line 2\u001B[0m\n\u001B[0;32m      1\u001B[0m \u001B[38;5;66;03m# excel 数据处理\u001B[39;00m\n\u001B[1;32m----> 2\u001B[0m excel_data \u001B[38;5;241m=\u001B[39m \u001B[43mpd\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mread_excel\u001B[49m\u001B[43m(\u001B[49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[38;5;124;43m../../resources/tips.xlsx\u001B[39;49m\u001B[38;5;124;43m'\u001B[39;49m\u001B[43m)\u001B[49m\n\u001B[0;32m      3\u001B[0m \u001B[38;5;28mprint\u001B[39m(\u001B[38;5;124m'\u001B[39m\u001B[38;5;124mexcel 数据处理 :\u001B[39m\u001B[38;5;124m'\u001B[39m, excel_data)\n\u001B[0;32m      4\u001B[0m result \u001B[38;5;241m=\u001B[39m excel_data\u001B[38;5;241m.\u001B[39mto_excel(\u001B[38;5;124m'\u001B[39m\u001B[38;5;124m../../resources/tips_out.xlsx\u001B[39m\u001B[38;5;124m'\u001B[39m, index\u001B[38;5;241m=\u001B[39m\u001B[38;5;28;01mFalse\u001B[39;00m)\n",
      "File \u001B[1;32mD:\\service\\python\\virtualenv\\fastapi\\lib\\site-packages\\pandas\\io\\excel\\_base.py:495\u001B[0m, in \u001B[0;36mread_excel\u001B[1;34m(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend, engine_kwargs)\u001B[0m\n\u001B[0;32m    493\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;129;01mnot\u001B[39;00m \u001B[38;5;28misinstance\u001B[39m(io, ExcelFile):\n\u001B[0;32m    494\u001B[0m     should_close \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;01mTrue\u001B[39;00m\n\u001B[1;32m--> 495\u001B[0m     io \u001B[38;5;241m=\u001B[39m \u001B[43mExcelFile\u001B[49m\u001B[43m(\u001B[49m\n\u001B[0;32m    496\u001B[0m \u001B[43m        \u001B[49m\u001B[43mio\u001B[49m\u001B[43m,\u001B[49m\n\u001B[0;32m    497\u001B[0m \u001B[43m        \u001B[49m\u001B[43mstorage_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mstorage_options\u001B[49m\u001B[43m,\u001B[49m\n\u001B[0;32m    498\u001B[0m \u001B[43m        \u001B[49m\u001B[43mengine\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mengine\u001B[49m\u001B[43m,\u001B[49m\n\u001B[0;32m    499\u001B[0m \u001B[43m        \u001B[49m\u001B[43mengine_kwargs\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mengine_kwargs\u001B[49m\u001B[43m,\u001B[49m\n\u001B[0;32m    500\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    501\u001B[0m \u001B[38;5;28;01melif\u001B[39;00m engine \u001B[38;5;129;01mand\u001B[39;00m engine \u001B[38;5;241m!=\u001B[39m io\u001B[38;5;241m.\u001B[39mengine:\n\u001B[0;32m    502\u001B[0m     \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mValueError\u001B[39;00m(\n\u001B[0;32m    503\u001B[0m         \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mEngine should not be specified when passing \u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m    504\u001B[0m         \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124man ExcelFile - ExcelFile already has the engine set\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m    505\u001B[0m     )\n",
      "File \u001B[1;32mD:\\service\\python\\virtualenv\\fastapi\\lib\\site-packages\\pandas\\io\\excel\\_base.py:1550\u001B[0m, in \u001B[0;36mExcelFile.__init__\u001B[1;34m(self, path_or_buffer, engine, storage_options, engine_kwargs)\u001B[0m\n\u001B[0;32m   1548\u001B[0m     ext \u001B[38;5;241m=\u001B[39m \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mxls\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m   1549\u001B[0m \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[1;32m-> 1550\u001B[0m     ext \u001B[38;5;241m=\u001B[39m \u001B[43minspect_excel_format\u001B[49m\u001B[43m(\u001B[49m\n\u001B[0;32m   1551\u001B[0m \u001B[43m        \u001B[49m\u001B[43mcontent_or_path\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mpath_or_buffer\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstorage_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mstorage_options\u001B[49m\n\u001B[0;32m   1552\u001B[0m \u001B[43m    \u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m   1553\u001B[0m     \u001B[38;5;28;01mif\u001B[39;00m ext \u001B[38;5;129;01mis\u001B[39;00m \u001B[38;5;28;01mNone\u001B[39;00m:\n\u001B[0;32m   1554\u001B[0m         \u001B[38;5;28;01mraise\u001B[39;00m \u001B[38;5;167;01mValueError\u001B[39;00m(\n\u001B[0;32m   1555\u001B[0m             \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124mExcel file format cannot be determined, you must specify \u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m   1556\u001B[0m             \u001B[38;5;124m\"\u001B[39m\u001B[38;5;124man engine manually.\u001B[39m\u001B[38;5;124m\"\u001B[39m\n\u001B[0;32m   1557\u001B[0m         )\n",
      "File \u001B[1;32mD:\\service\\python\\virtualenv\\fastapi\\lib\\site-packages\\pandas\\io\\excel\\_base.py:1402\u001B[0m, in \u001B[0;36minspect_excel_format\u001B[1;34m(content_or_path, storage_options)\u001B[0m\n\u001B[0;32m   1399\u001B[0m \u001B[38;5;28;01mif\u001B[39;00m \u001B[38;5;28misinstance\u001B[39m(content_or_path, \u001B[38;5;28mbytes\u001B[39m):\n\u001B[0;32m   1400\u001B[0m     content_or_path \u001B[38;5;241m=\u001B[39m BytesIO(content_or_path)\n\u001B[1;32m-> 1402\u001B[0m \u001B[38;5;28;01mwith\u001B[39;00m \u001B[43mget_handle\u001B[49m\u001B[43m(\u001B[49m\n\u001B[0;32m   1403\u001B[0m \u001B[43m    \u001B[49m\u001B[43mcontent_or_path\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[38;5;124;43mrb\u001B[39;49m\u001B[38;5;124;43m\"\u001B[39;49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mstorage_options\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[43mstorage_options\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mis_text\u001B[49m\u001B[38;5;241;43m=\u001B[39;49m\u001B[38;5;28;43;01mFalse\u001B[39;49;00m\n\u001B[0;32m   1404\u001B[0m \u001B[43m\u001B[49m\u001B[43m)\u001B[49m \u001B[38;5;28;01mas\u001B[39;00m handle:\n\u001B[0;32m   1405\u001B[0m     stream \u001B[38;5;241m=\u001B[39m handle\u001B[38;5;241m.\u001B[39mhandle\n\u001B[0;32m   1406\u001B[0m     stream\u001B[38;5;241m.\u001B[39mseek(\u001B[38;5;241m0\u001B[39m)\n",
      "File \u001B[1;32mD:\\service\\python\\virtualenv\\fastapi\\lib\\site-packages\\pandas\\io\\common.py:882\u001B[0m, in \u001B[0;36mget_handle\u001B[1;34m(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)\u001B[0m\n\u001B[0;32m    873\u001B[0m         handle \u001B[38;5;241m=\u001B[39m \u001B[38;5;28mopen\u001B[39m(\n\u001B[0;32m    874\u001B[0m             handle,\n\u001B[0;32m    875\u001B[0m             ioargs\u001B[38;5;241m.\u001B[39mmode,\n\u001B[1;32m   (...)\u001B[0m\n\u001B[0;32m    878\u001B[0m             newline\u001B[38;5;241m=\u001B[39m\u001B[38;5;124m\"\u001B[39m\u001B[38;5;124m\"\u001B[39m,\n\u001B[0;32m    879\u001B[0m         )\n\u001B[0;32m    880\u001B[0m     \u001B[38;5;28;01melse\u001B[39;00m:\n\u001B[0;32m    881\u001B[0m         \u001B[38;5;66;03m# Binary mode\u001B[39;00m\n\u001B[1;32m--> 882\u001B[0m         handle \u001B[38;5;241m=\u001B[39m \u001B[38;5;28;43mopen\u001B[39;49m\u001B[43m(\u001B[49m\u001B[43mhandle\u001B[49m\u001B[43m,\u001B[49m\u001B[43m \u001B[49m\u001B[43mioargs\u001B[49m\u001B[38;5;241;43m.\u001B[39;49m\u001B[43mmode\u001B[49m\u001B[43m)\u001B[49m\n\u001B[0;32m    883\u001B[0m     handles\u001B[38;5;241m.\u001B[39mappend(handle)\n\u001B[0;32m    885\u001B[0m \u001B[38;5;66;03m# Convert BytesIO or file objects passed with an encoding\u001B[39;00m\n",
      "\u001B[1;31mFileNotFoundError\u001B[0m: [Errno 2] No such file or directory: '../../resources/tips.xlsx'"
     ]
    }
   ],
   "source": [
    "# excel 数据处理\n",
    "excel_data = pd.read_excel('../../resources/tips.xlsx')\n",
    "print('excel 数据处理 :', excel_data)\n",
    "result = excel_data.to_excel('../../resources/tips_out.xlsx', index=False)\n",
    "print('excel 数据处理 :', result)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T01:21:54.037947200Z",
     "start_time": "2025-08-27T01:21:53.884845300Z"
    }
   },
   "id": "ffef25b9d39a963e"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# html 数据处理\n",
    "html_data = pd.read_html('../../resources/tips.html')\n",
    "print('html 数据处理 :', html_data)\n",
    "result = html_data.to_html('../../resources/tips_out.html')\n",
    "print('html 数据处理 :', result)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "a2247bbc980ea2ff"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 缺失值处理"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "aaddc0b83e38e320"
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失值处理 : 0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6     True\n",
      "7     True\n",
      "8    False\n",
      "9    False\n",
      "dtype: bool 0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6     True\n",
      "7     True\n",
      "8    False\n",
      "9    False\n",
      "dtype: bool\n"
     ]
    }
   ],
   "source": [
    "s = pd.Series([0, 1, np.nan, 3, 4, None, pd.NA, pd.NaT, 5, 6])\n",
    "print('缺失值处理 :', s.isna(), s.isnull())"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T01:28:07.809985900Z",
     "start_time": "2025-08-27T01:28:07.770114800Z"
    }
   },
   "id": "fef267578cd62f6b"
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失值处理 : 0    0\n",
      "1    0\n",
      "2    3\n",
      "3    0\n",
      "4    0\n",
      "5    3\n",
      "6    3\n",
      "7    3\n",
      "8    0\n",
      "9    0\n",
      "dtype: int64 0    0\n",
      "1    0\n",
      "2    3\n",
      "3    0\n",
      "4    0\n",
      "5    3\n",
      "6    3\n",
      "7    3\n",
      "8    0\n",
      "9    0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame([\n",
    "    [0, 1, np.nan, 3, 4, None, pd.NA, pd.NaT, 5, 6],\n",
    "    [0, 1, np.nan, 3, 4, None, pd.NA, pd.NaT, 5, 6],\n",
    "    [0, 1, np.nan, 3, 4, None, pd.NA, pd.NaT, 5, 6],\n",
    "])\n",
    "print('缺失值处理 :', df.isna().sum(), df.isnull().sum())"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:14:41.590123100Z",
     "start_time": "2025-08-27T02:14:41.563211Z"
    }
   },
   "id": "49e2d0423e9a07aa"
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据 :    0  1   2  3  4     5     6   7  8  9\n",
      "0  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "1  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "2  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "缺失值删除后 : Empty DataFrame\n",
      "Columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "# 删除缺失值，默认删除缺失值的那一行，\n",
    "# 可以设置属性，\n",
    "#   how = all: 删除所有行数据都为缺失值的行\n",
    "#   thresh = 1 : 删除行数据缺失值数量小于1的行\n",
    "#   axis = 1 ：删除列数据缺失值数量小于1的列\n",
    "#   subset = ['A', 'B'] : 删除指定列数据缺失值数量小于1的行\n",
    "# 删除缺失值\n",
    "print('原始数据 :', s)\n",
    "\n",
    "print('缺失值删除后 :', s.dropna())\n",
    "\n",
    "print('原始数据 :', df)\n",
    "print('缺失值删除后 :', df.dropna())"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T01:36:25.015852800Z",
     "start_time": "2025-08-27T01:36:24.985637300Z"
    }
   },
   "id": "f48ca6668b7b6305"
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据 :    0  1   2  3  4     5     6   7  8  9\n",
      "0  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "1  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "2  0  1 NaN  3  4  None  <NA> NaT  5  6\n",
      "使用前面的值填充列缺失值 :    0  1  2  3  4  5  6  7  8  9\n",
      "0  0  1  1  3  4  4  4  4  5  6\n",
      "1  0  1  1  3  4  4  4  4  5  6\n",
      "2  0  1  1  3  4  4  4  4  5  6\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_22868\\4161701743.py:9: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
      "  print('使用前面的值填充列缺失值 :', df.ffill(axis=1,limit_area=\"inside\"))  # 此方法存在警告\n"
     ]
    }
   ],
   "source": [
    "# 填充缺失值\n",
    "# print('原始数据 :', s)\n",
    "# print('填充缺失值 :', s.fillna(0)) # 此方法存在警告\n",
    "# print('填充缺失值 :', s.fillna('0')) \n",
    "print('原始数据 :', df)\n",
    "# print('填充缺失值 :', df.fillna(0,axis=1))  \n",
    "# print('使用字典来填充列缺失值 :', df.fillna({2: 2, 5: 5}))   # 此方法存在警告\n",
    "# print('使用字典来填充列缺失值 :', df.fillna({2: '2', 5: '5'}))  \n",
    "# print('使用前面的值填充列缺失值 :', df.ffill(axis=1))  # 此方法存在警告\n",
    "# print('使用后面的值填充列缺失值 :', df.bfill(axis=1))  # 此方法存在警告"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:21:37.559097900Z",
     "start_time": "2025-08-27T02:21:37.533408800Z"
    }
   },
   "id": "76341aa0bcc12094"
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  age city\n0  alice   26   NY\n1  alice   25   NY\n2    bob   30   LA\n3  alice   25   NY\n4   jack   35   SF\n5    bob   30   LA",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>age</th>\n      <th>city</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>alice</td>\n      <td>26</td>\n      <td>NY</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>alice</td>\n      <td>25</td>\n      <td>NY</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>bob</td>\n      <td>30</td>\n      <td>LA</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>alice</td>\n      <td>25</td>\n      <td>NY</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>jack</td>\n      <td>35</td>\n      <td>SF</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>bob</td>\n      <td>30</td>\n      <td>LA</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = {\n",
    "    'name': ['alice', 'alice', 'bob', 'alice', 'jack', 'bob'],\n",
    "    'age': [26, 25, 30, 25, 35, 30],\n",
    "    'city': ['NY', 'NY', 'LA', 'NY', 'SF', 'LA'],\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:26:48.132173900Z",
     "start_time": "2025-08-27T02:26:48.113022800Z"
    }
   },
   "id": "f2f8bc22e9e84151"
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "outputs": [
    {
     "data": {
      "text/plain": "0    False\n1    False\n2    False\n3     True\n4    False\n5     True\ndtype: bool"
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 检测重复行\n",
    "df.duplicated()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:27:29.855153900Z",
     "start_time": "2025-08-27T02:27:29.833366400Z"
    }
   },
   "id": "c6868983dc8bd845"
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  age city\n3  alice   25   NY\n4   jack   35   SF\n5    bob   30   LA",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>age</th>\n      <th>city</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3</th>\n      <td>alice</td>\n      <td>25</td>\n      <td>NY</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>jack</td>\n      <td>35</td>\n      <td>SF</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>bob</td>\n      <td>30</td>\n      <td>LA</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 去除重复行\n",
    "df.drop_duplicates()\n",
    "# 根据指定的列去重\n",
    "df.drop_duplicates(subset=['name'])\n",
    "# 保留最后一个，keep : 'first', 'last', \n",
    "df.drop_duplicates(subset=['name'], keep='last')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:29:40.842661600Z",
     "start_time": "2025-08-27T02:29:40.821854700Z"
    }
   },
   "id": "ddc703f4541c218a"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 数据类型转换"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "899af7235aff862a"
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "int64\n",
      "int16\n"
     ]
    }
   ],
   "source": [
    "# 数据类型转换\n",
    "print(df.age.dtype)\n",
    "# 必须重新赋值\n",
    "df.age = df.age.astype('int16')\n",
    "print(df.age.dtype)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T02:40:27.255607100Z",
     "start_time": "2025-08-27T02:40:27.236627200Z"
    }
   },
   "id": "8a8db7918578e095"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 数据变形"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "590fe822cc364ef"
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ID   name       科目  分数\n",
      "0   1  alice     Math  90\n",
      "1   2    bob     Math  85\n",
      "2   1  alice  English  80\n",
      "3   2    bob  English  92\n",
      "4   1  alice  Science  95\n",
      "5   2    bob  Science  89\n",
      "科目  ID   name  English  Math  Science\n",
      "0    1  alice       80    90       95\n",
      "1    2    bob       92    85       89\n"
     ]
    }
   ],
   "source": [
    "data = {\n",
    "    'ID': [1, 2, ],\n",
    "    'name': ['alice', 'bob'],\n",
    "    'Math': [90, 85],\n",
    "    'English': [80, 92],\n",
    "    'Science': [95, 89],\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "# df\n",
    "#  转置\n",
    "# df.T\n",
    "\n",
    "'''\n",
    "id  name    科目       分数\n",
    "1   alice   Math       90\n",
    "1   alice   English    80\n",
    "1   alice   Science    95\n",
    "'''\n",
    "# 宽表转长表\n",
    "# id_vars=['ID', 'name'] 唯一标识\n",
    "# var_name，value_name 成对出现\n",
    "df2 = pd.melt(df, id_vars=['ID', 'name'], var_name='科目', value_name='分数')\n",
    "df2.sort_values('name')\n",
    "print(df2)\n",
    "# 长表转宽表\n",
    "df3 = df2.pivot(index=['ID', 'name'], columns='科目', values='分数')\n",
    "print(df3)\n",
    "# print(df3.reset_index())"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T06:54:28.462742Z",
     "start_time": "2025-08-27T06:54:28.458481400Z"
    }
   },
   "id": "63d31ac59566ec47"
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "   ID         name  Math  English  Science first name last name\n0   1  alice smith    90       80       95      alice     smith\n1   2    bob smith    85       92       89        bob     smith",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>ID</th>\n      <th>name</th>\n      <th>Math</th>\n      <th>English</th>\n      <th>Science</th>\n      <th>first name</th>\n      <th>last name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>alice smith</td>\n      <td>90</td>\n      <td>80</td>\n      <td>95</td>\n      <td>alice</td>\n      <td>smith</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2</td>\n      <td>bob smith</td>\n      <td>85</td>\n      <td>92</td>\n      <td>89</td>\n      <td>bob</td>\n      <td>smith</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = {\n",
    "    'ID': [1, 2, ],\n",
    "    'name': ['alice smith', 'bob smith'],\n",
    "    'Math': [90, 85],\n",
    "    'English': [80, 92],\n",
    "    'Science': [95, 89],\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "# 分列\n",
    "# expand=True :变成多列\n",
    "df[['first name', 'last name']] = df.name.str.split(' ', expand=True)\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T06:05:37.981196200Z",
     "start_time": "2025-08-27T06:05:37.962011Z"
    }
   },
   "id": "678173cb17ae296f"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 数据分箱"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "534eee399c1f2694"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 1.等距离分段\n",
    "\n",
    "# 数据分箱,相当于分组\n",
    "# 把 df.Math 字段值分成3段，并返回分箱结果 \n",
    "cut = pd.cut(df.Math, bins=3)\n",
    "# cut.value_counts() # 统计每一段的数量\n",
    "\n",
    "# bins：分段区间,0-100，100-200，200-300，300-400，400-500\n",
    "# labels：分段区间的标签\n",
    "cut2 = pd.cut(df.Math, bins=[0, 100, 200, 300, 400, 500], labels=['0-100', '100-200', '200-300', '300-400', '400-500'])\n",
    "\n",
    "# 2.等频分段\n",
    "# 等分5段，就是每一段的数据数量一致\n",
    "pd.qcut(df.Math, q=5)"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "5dd50790dc8fd669"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 时间处理"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "fd0ee5b800874485"
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-06-22 00:00:00 <class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "2019 6 22\n",
      "季度： 2\n",
      "星期： 5 25\n",
      "是否是闰年： False\n",
      "转换成年度： 2019\n",
      "转换成季度： 2019Q2\n",
      "转换成月度： 2019-06\n",
      "转换成周度： 2019-06-17/2019-06-23\n",
      "转换成天度： 2019-06-22\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "d = pd.Timestamp('2019-06-22')\n",
    "print( d,type(d))\n",
    "print(d.year, d.month, d.day)\n",
    "print('季度：', d.quarter)\n",
    "print('星期：', d.weekday(), d.week)\n",
    "print('是否是闰年：', d.is_leap_year)\n",
    "print('转换成年度：', d.to_period(freq='Y'))\n",
    "print('转换成季度：', d.to_period(freq='Q'))\n",
    "print('转换成月度：', d.to_period(freq='M'))\n",
    "print('转换成周度：', d.to_period(freq='W'))\n",
    "print('转换成天度：', d.to_period(freq='D'))\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T06:33:56.759801900Z",
     "start_time": "2025-08-27T06:33:56.727751600Z"
    }
   },
   "id": "5eab0b1f6d440fcf"
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-06-22 00:00:00 <class 'pandas._libs.tslibs.timestamps.Timestamp'>\n"
     ]
    }
   ],
   "source": [
    "# 字符串转日期\n",
    "d = pd.to_datetime('2019-06-22')\n",
    "print(d,type(d))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2025-08-27T06:34:30.408739700Z",
     "start_time": "2025-08-27T06:34:30.395061800Z"
    }
   },
   "id": "5a1189d3577e0ef3"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 分组"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "c2789384bf4c6bca"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df.groupby('分组字段')['聚合的字段'].聚合函数()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "5f86214019c94535"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
